using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.ComponentModel;
using System.Reflection;
using System.Runtime.Serialization;
using System.Globalization;
using System.Data;
using System.Data.SqlClient;

namespace LibraryManagement
{
    #region DAL Class
    /// <summary>
    /// Simple Data Access component to make it easier for programmers
    /// to access the database.  Hides all the details around setting up
    /// Stored procedure parameters, opening, and closing the database
    /// connection.
    /// </summary>
    public class DAL : IDisposable
    {
        #region Static Fields
        private const String DatabaseName = "Database";
        private static Object _synchObject = new Object();
        private static Dictionary<String, List<SqlParameter>> _cache = new Dictionary<string, List<SqlParameter>>(25);
        #endregion

        #region Fields
        private SqlConnection _connection;
        private SqlTransaction _transaction;
        private Boolean _useTransaction;
        private Boolean _disposed;
        #endregion

        #region Constructors
        /// <summary>
        /// Initializes a new instance of the <see cref="T:DAL"/> class.
        /// </summary>
        public DAL() : this(String.Empty, false) { }
        /// <summary>
        /// Initializes a new instance of the <see cref="T:DALNew"/> class.
        /// </summary>
        /// <param name="useTransaction">if set to <c>true</c> [use transaction].</param>
        public DAL(Boolean useTransaction) : this(String.Empty, useTransaction) { }
        /// <summary>
        /// Initializes a new instance of the <see cref="T:DAL"/> class.
        /// </summary>
        /// <param name="connectString">The connect string.</param>
        public DAL(String connectString) : this(connectString, false) { }
        /// <summary>
        /// Initializes a new instance of the <see cref="T:DAL"/> class.
        /// </summary>
        /// <param name="connectString">The connect string.</param>
        /// <param name="useTransaction">if set to <c>true</c> [use transaction].</param>
        public DAL(String connectString, Boolean useTransaction)
        {
            if (String.IsNullOrEmpty(connectString))
                _connectString = System.Configuration.ConfigurationManager.ConnectionStrings["LIB"].ConnectionString;
            else
                _connectString = connectString;
            _useTransaction = useTransaction;
        }
        #endregion

        #region Properties
        /// <summary>
        /// Gets the connect string.
        /// </summary>
        /// <value>The connect string.</value>
        private String ConnectString
        {
            get
            {
                return _connectString;
            }
        }private String _connectString;

        #endregion

        #region Methods taking Stored Procedure name
        /// <summary>
        /// Uses the supplied Stored Procedure to return a <see cref="T:DataSet"/>
        /// with data from the SP call.
        /// </summary>
        /// <param name="spName">Name of the Stored Procedure.</param>
        /// <param name="args">Arguments to pass to the Stored Procedure.  The arguments must be in
        /// the same order as the Stored Procedure parameters.</param>
        /// <returns>Populated <see cref="T:DataSet"/> instance.</returns>
        public DataSet ExecuteDataSet(String spName, params Object[] args)
        {
            DataSet ds = new DataSet();
            using (SqlCommand cmd = CreateCommand())
            {
                try
                {
                    //set up the select command.
                    cmd.CommandText = spName;
                    cmd.CommandType = CommandType.StoredProcedure;

                    //Open the connection.
                    OpenConnection(cmd);

                    using (SqlDataAdapter adapter = new SqlDataAdapter())
                    {
                        adapter.SelectCommand = cmd;

                        //add in paramaters
                        SetUpParameters(adapter.SelectCommand, ConnectString, args);

                        //execute the command.
                        adapter.Fill(ds);
                    }

                }
                finally
                {
                    CloseConnection();
                }
            }
            return ds;
        }
        /// <summary>
        /// Uses the supplied Stored Procedure to return a <see cref="T:DataTable"/>
        /// with data from the SP call.
        /// </summary>
        /// <param name="spName">Name of the Stored Procedure.</param>
        /// <param name="args">Arguments to pass to the Stored Procedure.  The arguments must be in
        /// the same order as the Stored Procedure parameters.</param>
        /// <returns>Populated <see cref="T:DataTable"/> instance.</returns>
        public DataTable ExecuteDataTable(String spName, params Object[] args)
        {
            DataTable dt = new DataTable();
            using (SqlCommand cmd = CreateCommand())
            {
                try
                {
                    //set up the select command.
                    cmd.CommandText = spName;
                    cmd.CommandType = CommandType.StoredProcedure;

                    //Open the connection.
                    OpenConnection(cmd);

                    using (SqlDataAdapter adapter = new SqlDataAdapter())
                    {
                        adapter.SelectCommand = cmd;

                        //add in paramaters
                        SetUpParameters(adapter.SelectCommand, ConnectString, args);

                        //execute the command.
                        adapter.Fill(dt);
                    }

                }
                finally
                {
                    CloseConnection();
                }
            }
            return dt;
        }
        /// <summary>
        /// Uses the supplied Stored Procedure to return a <see cref="T:SqlDataReader"/>
        /// with data from the SP call.
        /// </summary>
        /// <param name="spName">Name of the Stored Procedure.</param>
        /// <param name="args">Arguments to pass to the Stored Procedure.  The arguments must be in
        /// the same order as the Stored Procedure parameters.</param>
        /// <returns>Populated <see cref="T:SqlDataReader"/> instance.</returns>
        /// <remarks>The <see cref="SqlDataReader"/> must be closed or disposed after use to
        /// close the underlying data connection.</remarks>
        public SqlDataReader ExecuteDataReader(String spName, params Object[] args)
        {
            return ExecuteDataReader(spName, CommandBehavior.CloseConnection, args);
        }
        /// <summary>
        /// Uses the supplied Stored Procedure to return a <see cref="T:SqlDataReader"/>
        /// with data from the SP call.
        /// </summary>
        /// <param name="spName">Name of the Stored Procedure.</param>
        /// <param name="behavior">Or'ed enumeration indicating the type of behavior requested.</param>
        /// <param name="args">Arguments to pass to the Stored Procedure.  The arguments must be in
        /// the same order as the Stored Procedure parameters.</param>
        /// <returns>
        /// Populated <see cref="T:SqlDataReader"/> instance.
        /// </returns>
        public SqlDataReader ExecuteDataReader(String spName, CommandBehavior behavior, params Object[] args)
        {
            SqlDataReader reader = null;
            using (SqlCommand cmd = CreateCommand())
            {
                //set up the select command.
                cmd.CommandText = spName;
                cmd.CommandType = CommandType.StoredProcedure;

                //open the connection.
                OpenConnection(cmd);

                //add in paramaters
                SetUpParameters(cmd, ConnectString, args);

                //execute the command.
                reader = cmd.ExecuteReader(behavior);
            }
            return reader;
        }
        /// <summary>
        /// Uses the supplied Stored Procedure to return the first column value
        /// from the first row returned from the SP call.
        /// </summary>
        /// <param name="spName">Name of the Stored Procedure.</param>
        /// <param name="args">Arguments to pass to the Stored Procedure.  The arguments must be in
        /// the same order as the Stored Procedure parameters.</param>
        /// <returns>The first column value from the first row returned from the SP call.</returns>
        public Object ExecuteScalar(String spName, params Object[] args)
        {
            Object value = null;
            using (SqlCommand cmd = CreateCommand())
            {
                try
                {
                    //set up the select command.
                    cmd.CommandText = spName;
                    cmd.CommandType = CommandType.StoredProcedure;

                    //open the connection.
                    OpenConnection(cmd);

                    //add in paramaters
                    SetUpParameters(cmd, ConnectString, args);

                    //execute the command.
                    value = cmd.ExecuteScalar();
                }
                finally
                {
                    CloseConnection();
                }
            }
            return value;
        }
        /// <summary>
        /// Calls a Stored Procedure that does not return a resultset.
        /// </summary>
        /// <param name="spName">Name of the Stored Procedure.</param>
        /// <param name="args">Arguments to pass to the Stored Procedure.  The arguments must be in
        /// the same order as the Stored Procedure parameters.</param>
        /// <returns>The number of rows affected by the SP call.</returns>
        public Int32 ExecuteNonQuery(String spName, params Object[] args)
        {
            Int32 value = -1;
            using (SqlCommand cmd = CreateCommand())
            {
                try
                {
                    //set up the select command.
                    cmd.CommandText = spName;
                    cmd.CommandType = CommandType.StoredProcedure;

                    //open the connection.
                    OpenConnection(cmd);

                    //add in paramaters
                    SetUpParameters(cmd, ConnectString, args);

                    //execute the command.
                    value = cmd.ExecuteNonQuery();
                }
                finally
                {
                    CloseConnection();
                }
            }
            return value;
        }
        /// <summary>
        /// Creates the specified entity type and loads it with the
        /// data from the supplied stored procedure.
        /// </summary>
        /// <param name="spName">Name of the sp.</param>
        /// <param name="args">The args.</param>
        /// <returns></returns>
        public Tentity LoadEntity<Tentity>(String spName, params Object[] args) where Tentity : DomainData, new()
        {
            Tentity entity = new Tentity();
            using (SqlDataReader reader = ExecuteDataReader(spName, args))
            {
                if (entity.LoadData(reader)) entity.AcceptChanges();
            }
            return entity;
        }
        /// <summary>
        /// Creates a list containing the specified entity type for
        /// each row in the returned from the supplied stored procedure.
        /// </summary>
        /// <param name="spName">Name of the stored procedure.</param>
        /// <param name="args">The arguments to the stored procedure.</param>
        /// <returns>List of entities.</returns>
        public List<Tentity> LoadEntityList<Tentity>(String spName, params Object[] args) where Tentity : DomainData, new()
        {
            List<Tentity> list = new List<Tentity>();
            using (SqlDataReader reader = ExecuteDataReader(spName, args))
            {
                while (true)
                {
                    Tentity entity = new Tentity();
                    if (entity.LoadData(reader))
                    {
                        entity.AcceptChanges();
                        list.Add(entity);
                    }
                    else
                    {
                        break;
                    }
                }
            }
            return list;
        }
        /// <summary>
        /// Updates the entity.
        /// </summary>
        /// <param name="spName">Name of the sp.</param>
        /// <param name="entity">The entity.</param>
        /// <returns>The number of rows affected by the update.</returns>
        public Int32 UpdateEntity(String spName, DomainData entity)
        {
            Int32 rowsAffected = 0;
            using (SqlCommand cmd = CreateCommand())
            {
                try
                {
                    //set up the select command.
                    cmd.CommandText = spName;
                    cmd.CommandType = CommandType.StoredProcedure;

                    //open the connection.
                    OpenConnection(cmd);

                    //add in paramaters
                    SetUpParameters(cmd, ConnectString, entity);

                    //execute the command.
                    rowsAffected = cmd.ExecuteNonQuery();
                    //update entity with any out parameter values.
                    //add the params to the command object.
                    foreach (SqlParameter param in cmd.Parameters)
                    {
                        if ((param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Output))
                        {
                            String paramName = param.ParameterName.StartsWith("@") ? param.ParameterName.Substring(1) : param.ParameterName;
                            if (entity.HasProperty(paramName))
                                entity.SetValue(paramName, param.Value);
                        }
                    }
                }
                catch (Exception ex)
                {
                    System.Diagnostics.Debug.Write(ex.Message);
                    throw;
                }
                finally
                {
                    CloseConnection();
                }
            }
            return rowsAffected;
        }
        #endregion

        #region Methods for SQL Statements
        /// <summary>
        /// Uses the supplied SQL statement to return a <see cref="T:DataSet"/>.
        /// </summary>
        /// <param name="sql">SQL statement to execute.</param>
        /// <param name="args">Arguments for the parameters within the SQL statement.
        /// The arguments must be in the same order as the parameters appear in the
        /// SQL statement.</param>
        /// <returns>Populated <see cref="T:DataSet"/> instance.</returns>
        public DataSet ExecuteDataSetSQL(String sql, params Object[] args)
        {
            DataSet ds = new DataSet();
            using (SqlCommand cmd = CreateCommand())
            {
                try
                {
                    //set up the select command.
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;

                    //Open the connection.
                    OpenConnection(cmd);

                    using (SqlDataAdapter adapter = new SqlDataAdapter())
                    {
                        adapter.SelectCommand = cmd;

                        //add in paramaters
                        SetupParametersSQL(sql, adapter.SelectCommand, args);

                        //execute the command.
                        adapter.Fill(ds);
                    }

                }
                finally
                {
                    CloseConnection();
                }
            }
            return ds;
        }
        /// <summary>
        /// Uses the supplied SQL Statement to return a <see cref="T:DataTable"/>.
        /// </summary>
        /// <param name="sql">SQL statement to execute.</param>
        /// <param name="args">Arguments for the parameters within the SQL statement.
        /// The arguments must be in the same order as the parameters appear in the
        /// SQL statement.</param>
        /// <returns>Populated <see cref="T:DataTable"/> instance.</returns>
        public DataTable ExecuteDataTableSQL(String sql, params Object[] args)
        {
            DataTable dt = new DataTable();
            using (SqlCommand cmd = CreateCommand())
            {
                try
                {
                    //set up the select command.
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;

                    //Open the connection.
                    OpenConnection(cmd);

                    using (SqlDataAdapter adapter = new SqlDataAdapter())
                    {
                        adapter.SelectCommand = cmd;

                        //add in paramaters
                        SetupParametersSQL(sql, adapter.SelectCommand, args);

                        //execute the command.
                        adapter.Fill(dt);
                    }

                }
                finally
                {
                    CloseConnection();
                }
            }
            return dt;
        }
        /// <summary>
        /// Uses the supplied SQL statement to return a <see cref="T:SqlDataReader"/>.
        /// </summary>
        /// <param name="sql">SQL statement to execute.</param>
        /// <param name="args">Arguments for the parameters within the SQL statement.
        /// The arguments must be in the same order as the parameters appear in the
        /// SQL statement.</param>
        /// <returns>Populated <see cref="T:SqlDataReader"/> instance.</returns>
        /// <remarks>The <see cref="SqlDataReader"/> must be closed or disposed after use to
        /// close the underlying data connection.</remarks>
        public SqlDataReader ExecuteDataReaderSQL(String sql, params Object[] args)
        {
            return ExecuteDataReaderSQL(sql, CommandBehavior.CloseConnection, args);
        }
        /// <summary>
        /// Uses the supplied SQL statement to return a <see cref="T:SqlDataReader"/>.
        /// </summary>
        /// <param name="sql">SQL statement to execute.</param>
        /// <param name="behavior">Or'ed enumeration indicating the type of behavior requested.</param>
        /// <param name="args">Arguments for the parameters within the SQL statement.
        /// The arguments must be in the same order as the parameters appear in the
        /// SQL statement.</param>
        /// <returns>
        /// Populated <see cref="T:SqlDataReader"/> instance.
        /// </returns>
        public SqlDataReader ExecuteDataReaderSQL(String sql, CommandBehavior behavior, params Object[] args)
        {
            SqlDataReader reader = null;
            using (SqlCommand cmd = CreateCommand())
            {
                //set up the select command.
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;

                //open the connection.
                OpenConnection(cmd);

                //add in paramaters
                SetupParametersSQL(sql, cmd, args);

                //execute the command.
                reader = cmd.ExecuteReader(behavior);
            }
            return reader;
        }
        /// <summary>
        /// Uses the supplied SQL statement to return the first column value
        /// from the first row returned from the SP call.
        /// </summary>
        /// <param name="sql">SQL statement to execute.</param>
        /// <param name="args">Arguments for the parameters within the SQL statement.
        /// The arguments must be in the same order as the parameters appear in the
        /// SQL statement.</param>
        /// <returns>The first column value from the first row returned from
        /// SQL statement.</returns>
        public Object ExecuteScalarSQL(String sql, params Object[] args)
        {
            Object value = null;
            using (SqlCommand cmd = CreateCommand())
            {
                try
                {
                    //set up the select command.
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;

                    //open the connection.
                    OpenConnection(cmd);

                    //add in paramaters
                    SetupParametersSQL(sql, cmd, args);

                    //execute the command.
                    value = cmd.ExecuteScalar();
                }
                finally
                {
                    CloseConnection();
                }
            }
            return value;
        }
        /// <summary>
        /// Executes the SQL statement that does not return a resultset.
        /// </summary>
        /// <param name="sql">SQL statement to execute.</param>
        /// <param name="args">Arguments for the parameters within the SQL statement.
        /// The arguments must be in the same order as the parameters appear in the
        /// SQL statement.</param>
        /// <returns>The number of rows affected by the SQL statement.</returns>
        public Int32 ExecuteNonQuerySQL(String sql, params Object[] args)
        {
            Int32 value = -1;
            using (SqlCommand cmd = CreateCommand())
            {
                try
                {
                    //set up the select command.
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;

                    //open the connection.
                    OpenConnection(cmd);

                    //add in parameters
                    SetupParametersSQL(sql, cmd, args);

                    //execute the command.
                    value = cmd.ExecuteNonQuery();
                }
                finally
                {
                    CloseConnection();
                }
            }
            return value;

        }



        /// <summary>
        /// Updates the entity.
        /// </summary>
        /// <param name="sql">The SQL statement to use to update the entity.</param>
        /// <param name="entity">The entity containing the data to persist.</param>
        /// <returns>The number of rows affected by the update.</returns>
        public Int32 UpdateEntitySQL(String sql, DomainData entity)
        {
            Int32 rowsAffected = 0;
            using (SqlCommand cmd = CreateCommand())
            {
                try
                {
                    //set up the select command.
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;

                    //open the connection.
                    OpenConnection(cmd);

                    //add in paramaters
                    SetupParametersSQL(sql, cmd, entity);

                    //execute the command.
                    rowsAffected = cmd.ExecuteNonQuery();
                    //update entity with any out parameter values.
                    foreach (SqlParameter param in cmd.Parameters)
                    {
                        if ((param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Output))
                        {
                            String paramName = param.ParameterName.StartsWith("@") ? param.ParameterName.Substring(1) : param.ParameterName;
                            if (entity.HasProperty(paramName))
                                entity.SetValue(paramName, param.Value);
                        }
                    }
                }
                finally
                {
                    CloseConnection();
                }

            }
            return rowsAffected;
        }
        /// <summary>
        /// Creates the specified entity type and loads it with the
        /// data from the supplied stored procedure.
        /// </summary>
        /// <param name="sql">The SQL statement returning the data for the entity.</param>
        /// <param name="args">The arguments to the SQL statement.</param>
        /// <returns>Loaded entity.</returns>
        public Tentity LoadEntitySQL<Tentity>(String sql, params Object[] args) where Tentity : DomainData, new()
        {
            Tentity entity = new Tentity();
            using (SqlDataReader reader = ExecuteDataReaderSQL(sql, args))
            {
                if (entity.LoadData(reader)) entity.AcceptChanges();
            }
            return entity;
        }
        /// <summary>
        /// Creates a list containing the specified entity type for
        /// each row in the returned from the supplied stored procedure.
        /// </summary>
        /// <param name="sql">The SQL statement returning the data for the entity.</param>
        /// <param name="args">The arguments to the SQL statement.</param>
        /// <returns>List of entities.</returns>
        public List<Tentity> LoadEntityListSQL<Tentity>(String sql, params Object[] args) where Tentity : DomainData, new()
        {
            List<Tentity> list = new List<Tentity>();
            using (SqlDataReader reader = ExecuteDataReaderSQL(sql, args))
            {
                while (true)
                {
                    Tentity entity = new Tentity();
                    if (entity.LoadData(reader))
                    {
                        entity.AcceptChanges();
                        list.Add(entity);
                    }
                    else
                    {
                        break;
                    }
                }
            }
            return list;
        }
        /// <summary>
        /// Commits the transaction.
        /// </summary>
        public void CommitTransaction()
        {
            try
            {
                if (_transaction != null)
                {
                    _transaction.Commit();
                    _transaction.Dispose();
                }
            }
            finally
            {
                if (_connection != null)
                {
                    try
                    {
                        _connection.Close();
                        _connection.Dispose();
                    }
                    catch (Exception) { }
                }
                _transaction = null;
                _connection = null;
            }
        }
        /// <summary>
        /// Rolls the transaction back.
        /// </summary>
        public void RollBackTransaction()
        {
            try
            {
                if (_transaction != null)
                {
                    _transaction.Rollback();
                    _transaction.Dispose();
                }
            }
            finally
            {
                if (_connection != null)
                {
                    try
                    {
                        _connection.Close();
                        _connection.Dispose();
                    }
                    catch (Exception) { }
                }
                _transaction = null;
                _connection = null;
            }
        }
        #endregion

        #region Helper Methods
        /// <summary>
        /// Creates the <see cref="T:SqlCommand"/> instance.
        /// </summary>
        /// <returns>A <see cref="T:SqlCommand"/> with a <see cref="T:Connection"/>.</returns>
        private SqlCommand CreateCommand()
        {
            if (_connection == null) _connection = new SqlConnection(ConnectString);
            SqlCommand cmd = _connection.CreateCommand();
            return cmd;
        }
        /// <summary>
        /// Opens the connection.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        private void OpenConnection(SqlCommand cmd)
        {
            if (!_useTransaction)
            {
                cmd.Connection.Open();
            }
            else
            {
                if (_transaction == null)
                {
                    cmd.Connection.Open();
                    _transaction = cmd.Connection.BeginTransaction();
                }
                cmd.Transaction = _transaction;
            }
        }
        /// <summary>
        /// Closes the connection.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        private void CloseConnection()
        {
            if (!_useTransaction)
            {
                try
                {
                    _connection.Close();
                    _connection.Dispose();
                }
                finally
                {
                    _connection = null;
                }
            }
        }
        #endregion

        #region Static Helper Methods
        /// <summary>
        /// Queries the database to determine the parameters for the requested stored
        /// procedure.
        /// </summary>
        /// <param name="spName">Stored procedure name.</param>
        /// <returns>
        /// 	<see cref="T:List<>"/> containing the paramters needed for the
        /// requested stored procedure.
        /// </returns>
        private static List<SqlParameter> DiscoverSpParameters(SqlCommand cmd, String connectString)
        {
            List<SqlParameter> dataParams = new List<SqlParameter>();
            using (SqlConnection connection = new SqlConnection(connectString))
            {
                SqlCommand sqlCmd = connection.CreateCommand();
                sqlCmd.CommandText = cmd.CommandText;
                sqlCmd.CommandType = CommandType.StoredProcedure;
                //open the connection
                try
                {
                    sqlCmd.Connection.Open();

                    //use the command builder to determine the sp param collection.
                    SqlCommandBuilder.DeriveParameters(sqlCmd);

                    //copy the parameters into our provider independent collection
                    //skip over @RETURN_VALUE which will be in the first position.
                    foreach (SqlParameter param in sqlCmd.Parameters)
                    {
                        if (param.Direction != ParameterDirection.ReturnValue)
                        {
                            SqlParameter clone = ((ICloneable)param).Clone() as SqlParameter;
                            if (clone != null)
                                dataParams.Add(clone);
                        }
                    }
                }
                finally
                {
                    sqlCmd.Connection.Close();
                }
            }
            return dataParams;
        }
        /// <summary>
        /// Sets the up the Stored Procedure parameters.
        /// </summary>
        /// <param name="cmd">The <see cref="T:SqlCommand"/> to add the parameters to.</param>
        /// <param name="args">The values for each parameter required on the Stored Procedure call.</param>
        private static void SetUpParameters(SqlCommand cmd, String connectString, Object[] args)
        {
            if (args != null && args.Length > 0)
            {
                //get the SP parameter list from the cache or
                //by discovery.
                List<SqlParameter> cachedParams = GetParameterList(cmd, connectString);

                //add the params to the command object.
                Int32 paramCt = args.Length;
                if (cachedParams.Count < paramCt) paramCt = cachedParams.Count;
                for (Int32 idx = 0; idx < paramCt; idx++)
                {
                    cmd.Parameters.AddWithValue(cachedParams[idx].ParameterName, args[idx]);
                }
            }
        }
        /// <summary>
        /// Sets the up the Stored Procedure parameters.
        /// </summary>
        /// <param name="cmd">The <see cref="T:SqlCommand"/> to setup.</param>
        /// <param name="entity">The entity that will be used to populate the command
        /// parameters.</param>
        private static void SetUpParameters(SqlCommand cmd, String connectString, DomainData entity)
        {
            if (entity != null)
            {
                //get the SP parameter list from the cache or
                //by discovery.
                List<SqlParameter> cachedParams = GetParameterList(cmd, connectString);

                //add the params to the command object.
                Dictionary<String, String> map = entity.GetColumnMap();
                foreach (SqlParameter param in cachedParams)
                {
                    if ((param.Direction == ParameterDirection.Input || param.Direction == ParameterDirection.InputOutput))
                    {
                        try
                        {
                            String paramName = param.ParameterName.StartsWith("@") ? param.ParameterName.Substring(1).ToUpper() : param.ParameterName.ToUpper();
                            if (map.ContainsKey(paramName))
                            {
                                SqlParameter paramNew = ((ICloneable)param).Clone() as SqlParameter;
                                paramNew.Value = entity.GetValue(map[paramName]);
                                cmd.Parameters.Add(paramNew);
                                //cmd.Parameters.AddWithValue(param.ParameterName, entity.GetValue(map[paramName]));
                            }
                        }
                        catch (Exception ex)
                        {
                            System.Diagnostics.Debug.Write(ex.Message);
                            throw;
                        }
                    }
                }
            }
        }
        /// <summary>
        /// Gets the parameter list.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <returns></returns>
        private static List<SqlParameter> GetParameterList(SqlCommand cmd, String connectString)
        {
            String key = BuildKey(cmd);
            List<SqlParameter> cachedParams = null;
            if (_cache.ContainsKey(key))
            {
                //return param collection
                cachedParams = _cache[key];
            }
            else
            {
                //discover list
                cachedParams = DiscoverSpParameters(cmd, connectString);
                lock (_cache)
                {
                    if (!_cache.ContainsKey(key)) _cache.Add(key, cachedParams);
                }
            }
            return cachedParams;
        }
        /// <summary>
        /// Adds named parameters to the supplied <see cref="T:SqlCommand"/> object
        /// by scanning the SQL statement.
        /// </summary>
        /// <param name="sql">The SQL statement to scan.</param>
        /// <param name="cmd">The <see cref="T:SqlCommand"/> to add the
        /// parameters to.</param>
        /// <param name="args">Array of parameter values.</param>
        private static void SetupParametersSQL(String sql, SqlCommand cmd, Object[] args)
        {
            //scan the SQL statement and get a list of parameters names
            String[] pNames = ParseSQL(sql);

            //add in paramaters
            Int32 max = pNames.Length < args.Length ? pNames.Length : args.Length;
            for (Int32 idx = 0; idx < max; idx++)
            {
                cmd.Parameters.AddWithValue(pNames[idx], args[idx]);
            }
        }
        /// <summary>
        /// Adds named parameters to the supplied <see cref="T:SqlCommand"/> object
        /// by scanning the SQL statement.
        /// </summary>
        /// <param name="sql">The SQL statement to scan.</param>
        /// <param name="cmd">The <see cref="T:SqlCommand"/> to add the
        /// parameters to.</param>
        /// <param name="entity">The entity to extract the parameter values from.</param>
        private static void SetupParametersSQL(String sql, SqlCommand cmd, DomainData entity)
        {
            //scan the SQL statement and get a list of parameters names
            String[] pNames = ParseSQL(sql);

            //add in the parameters
            Dictionary<String, String> map = entity.GetColumnMap();
            foreach (String name in pNames)
            {
                String propName = name.StartsWith("@") ? name.Substring(1) : name;
                if (map.ContainsKey(propName))
                {
                    cmd.Parameters.AddWithValue(propName, entity.GetValue(map[propName]));
                }
            }
        }
        /// <summary>
        /// Builds the key used to store the Dataparameter set within the cache.
        /// </summary>
        /// <param name="spName">Stored procedure name.</param>
        /// <returns>
        /// Key used to store the set within the cache.
        /// </returns>
        private static String BuildKey(SqlCommand cmd)
        {
            return cmd.Connection.ConnectionString + ":" + cmd.CommandText;
        }
        /// <summary>
        /// Parses the SQL and returns the list
        /// of parameters defined within the SQL statement.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <returns>List of parameters defined within the SQL statement.</returns>
        /// <remarks>Assumes the parameters are in "@name" format, not the odbc style "?".</remarks>
        private static String[] ParseSQL(String sql)
        {
            const String del = @"=<>!,) ";
            List<String> parms = new List<string>();
            Int32 spos = -1;
            for (Int32 cpos = 0; cpos < sql.Length; cpos++)
            {
                //check to see if we are scanning for the end of a
                //parameter name.
                if (spos > -1)
                {
                    //check to see if we are at the end of a parameter name
                    if (del.IndexOf(sql[cpos]) > 0)
                    {
                        //add to list
                        String pName = sql.Substring(spos, (cpos - spos));
                        parms.Add(pName);
                        spos = -1;
                    }
                }
                else
                {
                    //check to see if we are at the beginning of a parameter name
                    if (sql[cpos] == '@') spos = cpos;
                }
            }
            //check to see if spos is still set because we could have ended without hitting
            //a paramter delimiter.
            if (spos > -1)
            {
                //add to list
                String pName = sql.Substring(spos, (sql.Length - spos));
                parms.Add(pName);
            }
            return parms.ToArray();
        }
        #endregion

        #region IDisposable Members

        /// <summary>
        /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
        }
        /// <summary>
        /// Disposes the specified disposing.
        /// </summary>
        /// <param name="disposing">if set to <c>true</c> [disposing].</param>
        protected virtual void Dispose(Boolean disposing)
        {
            if (!_disposed)
            {
                if (disposing)
                {
                    //check the transaction and connection objects
                    RollBackTransaction();
                }
            }
            _disposed = true;
        }

        #endregion
    }
    #endregion
}
